/****** Object:  StoredProcedure [dbo].[sp_draft_calc]    Script Date: 11/11/2009 13:20:22 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_draft_calc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_draft_calc]
GO
/****** Object:  StoredProcedure [dbo].[sp_draft_calc]    Script Date: 11/11/2009 13:18:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***********************************************************************
* sp Name: dbo.sp_draft_calc
* Created: 20100727, RGM
* Purpose: realizar el calculo del monto a impactar en el draft
*
* Modified: yyyymmdd user, data 
*
* HistoryDate.
* yyyymmdd User, data
* 
* Note: nothing
* 
***********************************************************************/
CREATE PROCEDURE [dbo].[sp_draft_calc]
	@povId		INT, 
	@periods	INT, 
	@DEBUG		INT = 0
AS

/*
	error handling
*/
BEGIN TRY
	DECLARE
		@TranStarted BIT
	SET @TranStarted = 0

	IF( @@TRANCOUNT = 0 ) BEGIN
		BEGIN TRANSACTION
		SET @TranStarted = 1
	END ELSE BEGIN
		SET @TranStarted = 0
	END

	SET NOCOUNT ON
-------------------------------------------------------
--[BEGIN] SP section
DECLARE @text VARCHAR(512)

SET @text = '	------ Ejecutando sp_draft_calc ' 
		+ CONVERT(VARCHAR(10), @povId) + ', ' +
		+ CONVERT(VARCHAR(10), @periods)

IF @DEBUG = 1
	PRINT @text

DECLARE
	@bushelAmount	DECIMAL (18, 4), 
	@baseId			INT, 
	@baseAmount		INT, 
	@tonCost		DECIMAL (18, 4), 
	@tons			DECIMAL (18, 4), 
	@taxes			DECIMAL (18, 4), 
	@poAmount		DECIMAL (18, 4), 
	@poTax			DECIMAL (18, 4), 
	@toDraft		DECIMAL (18, 4), 
	@result			INT,
	@message		VARCHAR (1024), 

-- manejo de entorno
	@RCOUNT			INT

SELECT @baseId = cost_element_id
	FROM costs_elements
	WHERE short_name = 'BASES'


SELECT @tons = pov.capacity, 
		@baseAmount = pocv.amount, 
		@bushelAmount = bv.amount, 
		@taxes = bav.tax1 + bav.tax2
	FROM purchases_orders_versions pov, vw_periods_versions pv, 
		bushels_versions bv, balances_versions bav, 
		purchases_orders_costs_versions pocv
	WHERE pov.biweek_period_version_id = pv.biweek_period_version_id
		AND pv.month_period_version_id = bv.month_period_version_id
		AND pv.month_period_version_id = bav.month_period_version_id
		AND pov.purchase_order_version_id = pocv.purchase_order_version_id
		AND pov.purchase_order_version_id = @povId
		AND pocv.cost_element_id = @baseId

--review values
--SELECT @tons capacidad, @baseAmount bases, @bushelAmount bushel, @taxes impuestos

IF @bushelAmount > 0 BEGIN
	SELECT @tonCost = @baseAmount + dbo.fn_unit_price_convertion('BUS', 'TON', @bushelAmount)

	SELECT @poAmount = @tons * @tonCost

	SELECT @poTax = ((@poAmount * @taxes) / 12 ) * @periods

	SELECT @toDraft = @poAmount + @poTax

	SET @result		= 1
	SET @message	= 'Calculo completado correctamente'
END ELSE BEGIN
	SET @result		= 0
	SET @message	= 'No se puede realizar debido a falta de valor del Bushel'
	SET @toDraft	= 0
END

/*
INSERT INTO table1 (field1, field2)
	SELECT field1, field2
		FROM table_x

SET @RCOUNT = @@ROWCOUNT
IF @DEBUG = 1
	PRINT 'I:table1 [' + CONVERT(VARCHAR(10), @RCOUNT) + ']'

--se actualizan los ids de los edos de cuenta que si caigan en la version
UPDATE table2 SET field1 = 3

SET @RCOUNT = @@ROWCOUNT
IF @DEBUG = 1
	PRINT 'U:table2 [' + CONVERT(VARCHAR(10), @RCOUNT) + ']'


IF @DEBUG = 1
	PRINT 'END:' + @text
*/

--[END] SP section
-------------------------------------------------------
	SET NOCOUNT OFF

	IF( @TranStarted = 1 ) BEGIN
		SET @TranStarted = 0
		COMMIT TRANSACTION
	END

	SELECT @result AS result, @message AS message, @toDraft AS amount
END TRY BEGIN CATCH
	DECLARE 
		@ErrorMessage	NVARCHAR(4000), 
		@ErrorNumber	INT, 
		@ErrorSeverity	INT, 
		@ErrorState		INT

	SELECT @ErrorMessage = ERROR_MESSAGE(),
				@ErrorSeverity = ERROR_SEVERITY(),
				@ErrorState = ERROR_STATE(),
				@ErrorNumber = ERROR_NUMBER()

	IF( @TranStarted = 1 ) BEGIN
		SET @TranStarted = 0
		ROLLBACK TRANSACTION
	END

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
	SELECT @result AS result, @message AS message, @toDraft AS amount
END CATCH 



